package com.centit.airplanebaseinfo.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import com.alibaba.fastjson.JSON;
import com.centit.framework.core.dao.CodeBook;
import com.centit.framework.jdbc.dao.BaseDaoImpl;
import com.centit.framework.jdbc.dao.DatabaseOptUtils;
import com.centit.support.database.utils.PageDesc;
import com.centit.support.database.utils.QueryAndNamedParams;
import com.centit.support.database.utils.QueryUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.centit.airplanebaseinfo.po.AirplaneBaseinfo;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;



/**
 * AirplaneBaseinfoDao  Repository.
 * create by scaffold 2020-03-13 
 * @author codefan@sina.com
 * 飞机基础信息飞机基础信息   
*/

@Repository
public class AirplaneBaseinfoDao extends BaseDaoImpl<AirplaneBaseinfo,java.lang.String>
	{

	public static final Log log = LogFactory.getLog(AirplaneBaseinfoDao.class);
	
	@Override
	public Map<String, String> getFilterField() {
		if( filterField == null){
			filterField = new HashMap<String, String>();

			filterField.put("recId" , CodeBook.EQUAL_HQL_ID);


			filterField.put("gljh" , CodeBook.EQUAL_HQL_ID);

			filterField.put("ccxlh" , CodeBook.LIKE_HQL_ID);

			filterField.put("zch" , CodeBook.EQUAL_HQL_ID);

			filterField.put("fsn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("airZt" , CodeBook.EQUAL_HQL_ID);

			filterField.put("nature" , CodeBook.EQUAL_HQL_ID);

			filterField.put("pw" , CodeBook.EQUAL_HQL_ID);

			filterField.put("startTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("endTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("sign" , CodeBook.LIKE_HQL_ID);

			filterField.put("airModel" , CodeBook.LIKE_HQL_ID);

			filterField.put("airZzs" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("flowInsId" , CodeBook.EQUAL_HQL_ID);

			filterField.put("outSn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("proctionDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("useDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("offDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("engineS" , CodeBook.EQUAL_HQL_ID);

			filterField.put("engine" , CodeBook.EQUAL_HQL_ID);

			filterField.put("oneSn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("twoSn" , CodeBook.EQUAL_HQL_ID);

			filterField.put("gjNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("gjDate" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("htMoney" , CodeBook.EQUAL_HQL_ID);

			filterField.put("bzfj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("haveName" , CodeBook.LIKE_HQL_ID);

			filterField.put("haveAddr" , CodeBook.EQUAL_HQL_ID);

			filterField.put("yyName" , CodeBook.EQUAL_HQL_ID);

			filterField.put("yyAddr" , CodeBook.EQUAL_HQL_ID);

			filterField.put("shNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("qfDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("hjNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("addrNoHex" , CodeBook.EQUAL_HQL_ID);

			filterField.put("addrNoOct" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtNo" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtyxDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtbfDate" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dtz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("shz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("gjz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("ht" , CodeBook.EQUAL_HQL_ID);

			filterField.put("ht2htsqxx" , CodeBook.EQUAL_HQL_ID);

			filterField.put("fjyz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("fjcz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("bxxx" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zjbz" , CodeBook.EQUAL_HQL_ID);

			filterField.put("mqzj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zjzfqj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("hl" , CodeBook.EQUAL_HQL_ID);

			filterField.put("jrygrq" , CodeBook.EQUAL_HQL_ID);

			filterField.put("tcygrq" , CodeBook.EQUAL_HQL_ID);

			filterField.put("apu" , CodeBook.EQUAL_HQL_ID);

			filterField.put("hud" , CodeBook.EQUAL_HQL_ID);

			filterField.put("wifi" , CodeBook.EQUAL_HQL_ID);

			filterField.put("maxZl" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zyqdj" , CodeBook.EQUAL_HQL_ID);

			filterField.put("idera" , CodeBook.EQUAL_HQL_ID);

			filterField.put("zwbj" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("djsj" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("status" , CodeBook.EQUAL_HQL_ID);

			filterField.put("dataValid" , CodeBook.EQUAL_HQL_ID);

			filterField.put("insUsercode" , CodeBook.EQUAL_HQL_ID);

			filterField.put("insTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("editUsercode" , CodeBook.EQUAL_HQL_ID);

			filterField.put("editTime" , CodeBook.EQUAL_HQL_ID);

			filterField.put("delUsercode" , CodeBook.EQUAL_HQL_ID);

			filterField.put("delTime" , CodeBook.EQUAL_HQL_ID);
			
			filterField.put("czType" , CodeBook.EQUAL_HQL_ID);

			filterField.put("tzrqqbnnjhdx" , CodeBook.EQUAL_HQL_ID);

			filterField.put("tzrqqbnnyqdx" , CodeBook.EQUAL_HQL_ID);

			//小今天报警
			filterField.put("dtzzjkbjrq" , " to_date(:dtzzjkbjrq, 'yyyy-MM-dd')>=DTZZJKBJRQ ");

		}
		return filterField;
	} 
	public AirplaneBaseinfo getAirplaneBaseinfoByCcxlh(String ccxlh) {
		String sql = "SELECT * FROM T_AIRPLANE_BASEINFO WHERE CCXLH = '"+ccxlh+"' AND DATA_VALID='T'";
		AirplaneBaseinfo airplaneBaseinfo = new AirplaneBaseinfo();
		JSONObject obj = DatabaseOptUtils.getObjectBySqlAsJson(this, sql);
		if(obj!=null){
			airplaneBaseinfo = JSONObject.toJavaObject(obj, AirplaneBaseinfo.class);
		}
		return airplaneBaseinfo;
	}
	
	/**
	 * 	飞机引进情况---- 统计报表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray getFjyjqlList(Map<String, Object> filterMap, PageDesc pageDesc) {
		String sql = "WITH airtable AS (SELECT extract (year from TO_DATE(DJSJ,'YYYY-MM-DD HH24:MI:SS')) nf ,AIR_MODEL jx,ZCH zch,DJSJ yjsj FROM T_AIRPLANE_BASEINFO)"
				+ "SELECT * FROM airtable WHERE 1=1 "
				+ "[ :nf | and nf > (extract (year from SYSDATE)-:nf)]"
				+ "[ :starnf | and nf >= :starnf ]"
				+ "[ :endnf | and nf <= :endnf ]"
				+ " ORDER BY nf ASC";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), pageDesc);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
	
	/**
	 * 	规划部列表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray getAirplaneBaseinfoList(Map<String, Object> filterMap, PageDesc pageDesc) {
		String sql = " SELECT a.REC_ID,MAX(a.DJSJ) DJSJ,MAX(a.AIR_MODEL) AIR_MODEL,MAX(a.SIGN) SIGN," +
				"MAX(a.CCXLH) CCXLH,MAX(a.NATURE) NATURE," +
				"MAX(a.AIR_ZT)AIR_ZT,MAX(a.HAVE_NAME) HAVE_NAME,MAX(a.DT_NO) DT_NO," +
				"MAX(a.FLOW_INS_ID) FLOW_INS_ID,MAX(a.END_TIME) END_TIME,MAX(a.MAX_ZL) MAX_ZL,"+
					"	DECODE(MAX(a.STATUS), '0','未提交','2','已完成','3','已注销') STATUS"+
					"	FROM T_AIRPLANE_BASEINFO a "+
				/*	"	LEFT JOIN DHFLOW.WF_FLOW_INSTANCE f ON a.REC_ID = f.FLOW_OPT_TAG "+
					"	LEFT JOIN DHFLOW.WF_NODE_INSTANCE ni ON ni.FLOW_INST_ID = f.FLOW_INST_ID AND NI.NODE_STATE='N' "+
					"	LEFT JOIN DHFLOW.WF_NODE n on n.node_id=ni.node_id  "+*/
					"	WHERE a.DATA_VALID = 'T'"+
					" [ :(like)airModel | and a.AIR_MODEL like :airModel ]  "+
					" [ :(like)sign | and a.SIGN like :sign ]  "+
					" [ :(like)ccxlh | and a.CCXLH like :ccxlh ]  "+
					" [ :nature| and a.NATURE  = :nature ]" +
					" [ :maxZl| and a.MAX_ZL  = :maxZl ]" +
					" [ :airZt| and a.AIR_ZT  = :airZt ]" +
					" [ :status| and a.STATUS  = :status ]" +
					" [ :dtNo| and a.DT_NO  = :dtNo ]" +
					" [ :haveName| and a.HAVE_NAME  = :haveName ]" +
					" [ :proctionDate| and a.PROCTION_Date =  to_date( :proctionDate,'YYYY-MM-DD') ]" +
					/*"	GROUP BY a.REC_ID,a.DJSJ,a.AIR_MODEL,a.SIGN,a.FLOW_INS_ID,a.CCXLH,a.NATURE,a.AIR_ZT,a.HAVE_NAME,a.STATUS,a.DT_NO,a.END_TIME,a.MAX_ZL   [ :orderby| order by :orderby ]"*/
					"	GROUP BY a.REC_ID   [ :orderby| order by :orderby ]";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		System.out.println("-----params.getQuery()-----"+ JSON.toJSONString(params.getQuery()));
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), pageDesc);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}

		/**
		 * 	机组信息查询
		 * @param filterMap
		 * @param pageDesc
		 * @return
		 */
		public JSONArray getAirplaneBaseinfoListNew(Map<String, Object> filterMap, PageDesc pageDesc) {
			String sql = " SELECT a.* ,"+
					"	DECODE(a.STATUS, '0','未提交','2','已完成','3','已注销') STATUS_VAL"+
					"	FROM T_AIRPLANE_BASEINFO a "+
					"	WHERE a.DATA_VALID = 'T'"+
					" [ :(like)airModel | and a.AIR_MODEL like :airModel ]  "+
					" [ :(like)gjNo | and a.GJ_NO like :gjNo ]  "+
					" [ :(like)sign | and a.SIGN like :sign ]  "+
					" [ :(like)ccxlh | and a.CCXLH like :ccxlh ]  "+
					" [ :nature| and a.NATURE  = :nature ]" +
					" [ :maxZl| and a.MAX_ZL  = :maxZl ]" +
					" [ :airZt| and a.AIR_ZT  = :airZt ]" +
					" [ :status| and a.STATUS  = :status ]" +
					" [ :dtNo| and a.DT_NO  = :dtNo ]" +
					" [ :haveName| and a.HAVE_NAME  = :haveName ]" +
					" [ :proctionDate| and a.PROCTION_Date =  to_date( :proctionDate,'YYYY-MM-DD') ]" +
					/*"	GROUP BY a.REC_ID,a.DJSJ,a.AIR_MODEL,a.SIGN,a.FLOW_INS_ID,a.CCXLH,a.NATURE,a.AIR_ZT,a.HAVE_NAME,a.STATUS,a.DT_NO,a.END_TIME,a.MAX_ZL   [ :orderby| order by :orderby ]"*/
					" [ :orderby| order by :orderby ]";
			QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
			System.out.println("-----params.getQuery()-----"+ JSON.toJSONString(params.getQuery()));
			JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), pageDesc);
			if (jsonArray == null) {
				return new JSONArray();
			}
			return jsonArray;
		}
	
	/**
	 * 	插入工作流
	 * @param
	 * @param
	 * @return
	 */
	public void insertFlow(Long flowInstId,String a,String type) {
		String sql = "insert into DHFLOW.WF_FLOW_VARIABLE values(?,?,?,?,?)";
		Object[] objects = {flowInstId,"A",a,type,"s"};
		DatabaseOptUtils.doExecuteSql(this, sql, objects);
	}
	
	/**
	 * 	插入工作流日志
	 * @param
	 * @param
	 * @return
	 */
	public void insertFlowLog(String id,Long flowInstId,Long nodeInstId,String unitCode,String userCode,Date transIdea,String nodeName,String nodeCode) {
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		String dateStr=sdf.format(new Date());
		String sql = "insert into DHFLOW.OPT_IDEA_INFO values('"+id+"',"+nodeInstId+","+flowInstId+",'"+unitCode+"','','"+userCode+"','',TO_DATE('"+dateStr+"','yyyy-MM-dd HH24:mi:ss'),'T','同意','','"+nodeName+"','','"+nodeCode+"',NULL)";
		//Object[] objects = {id,nodeInstId,flowInstId,unitCode,"",userCode,"","TO_DATE('"+dateStr+"','yyyy-MM-dd HH24:mi:ss')","T","同意","",nodeName,"",nodeCode};
		DatabaseOptUtils.doExecuteSql(this, sql);
	}
	/**
	 * 获取待办列表
	 * @param filterMap
	 * @param pageDesc
	 * @return
	 */
	public JSONArray listDb(Map<String, Object> filterMap, PageDesc pageDesc) {
		String sql="select a.*,j.GH_NAME,i.create_time,i.unit_name,i.idea_type from T_AIRPLANE_BASEINFO a left join T_IDEA_INFO i on a.FLOW_INS_ID=i.FLOW_REC_ID "
					+" left join T_JDGHGL j on a.GLJH=j.REC_ID  "
					+" where  i.DATA_VALID='T' and i.idea_code='待办理' "+
					" [ :(like)airModel | and a.AIR_MODEL like :airModel ]  "+
					" [ :(like)sign | and a.SIGN like :sign ]  "+
					" [ :(like)ccxlh | and a.CCXLH like :ccxlh ]  "+
					" [ :nature| and a.NATURE  = :nature ]" +
					" [ :airZt| and a.AIR_ZT  = :airZt ]" 
					+ " [ :userCode | and i.USER_CODE= :userCode ]  "
					+ " order by i.CREATE_TIME  desc";
		QueryAndNamedParams params = QueryUtils.translateQuery(sql, filterMap);
		JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), pageDesc);
		if (jsonArray == null) {
			return new JSONArray();
		}
		return jsonArray;
	}
}
